
* =======================================================================================================================================================
*
* Code Description: 
* This codefile pulls the necessary data for Table 6 (security-level effects during March 2020)
*
* =======================================================================================================================================================
*
* Major output:
* preparation for Table 6: Security-level effects.
* =======================================================================================================================================================
*
* General disclaimer:
* This file directory produces replication code for "Connected Funds". 
* Because we cannot share the underlying data provided by the Bundesbank's Research Data and Service Centre (RDSC) and other subscription data sources, 
* we have included pseudo data to show how the raw data are formatted. 
* Other researchers can go through a similar approval and subscription process to obtain the underlying data. (2023-03-23)
*
* =======================================================================================================================================================


// Paths
global Path "C:\ConnectedFunds_Codebase\"
global Codes "Codes" 
sysdir set STBPLUS "${Path}Code\Ado" 

clear
est clear

cd "${Path}Data\CSDB\"

**** generate list of unique isins first

clear
import delimited "${Path}Data\SecurityLevelEffects\SecurityLevel_Returns.txt"

keep isin
duplicates drop isin, force
rename isin ISIN

save "${Path}Data\SecurityLevelEffects\ISIN_List_Securities.dta", replace

**** prepare data from CSDB: keep only data on securities that are held by DE funds (i.e. those securities included in "ISIN_List_Securities.dta")

clear

use "${Path}Data\SecurityLevelEffects\ISIN_List_Securities.dta"


global dates "200910 200911 200912 201001 201002 201003 201004 201005 201006 201007 201008 201009 201010 201011 201012 201101 201102 201103 201104 201105 201106 201107 201108 201109 201110 201111 201112 201201 201202 201203 201204 201205 201206 201207 201208 201209 201210 201211 201212 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312 201401 201402 201403 201404 201405 201406 201407 201408 201409 201410 201411 201412 201501 201502 201503 201504 201505 201506 201507 201508 201509 201510 201511 201512 201601 201602 201603 201604 201605 201606 201607 201608 201609 201610 201611 201612 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 201802 201803 201804 201805 201806 201807 201808 201809 201810 201811 201812 201901 201902 201903 201904 201905 201906 201907 201908 201909 201910 201911 201912 202001 202002 202003 202004 202005 202006"


foreach x of global dates{
	preserve
	
	* Load raw CSDB data
	usegz "${Path}Data\CSDB\raw\CSDB200903202112_SDS2_`x'.dta.gz"
	
	merge 1:1 ISIN using "${Path}Data\SecurityLevelEffects\ISIN_List_Securities.dta"
	keep if _merge ==3
	drop _merge

	save "${Path}Data\SecurityLevelEffects\tmp_`x'.dta", replace

restore
}


clear

global dates "200911 200912 201001 201002 201003 201004 201005 201006 201007 201008 201009 201010 201011 201012 201101 201102 201103 201104 201105 201106 201107 201108 201109 201110 201111 201112 201201 201202 201203 201204 201205 201206 201207 201208 201209 201210 201211 201212 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312 201401 201402 201403 201404 201405 201406 201407 201408 201409 201410 201411 201412 201501 201502 201503 201504 201505 201506 201507 201508 201509 201510 201511 201512 201601 201602 201603 201604 201605 201606 201607 201608 201609 201610 201611 201612 201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712 201801 201802 201803 201804 201805 201806 201807 201808 201809 201810 201811 201812 201901 201902 201903 201904 201905 201906 201907 201908 201909 201910 201911 201912 202001 202002 202003 202004 202005 202006"

use "${Path}Data\SecurityLevelEffects\tmp_200910.dta"

foreach x of global dates{
	append using "${Path}Data\SecurityLevelEffects\tmp_`x'.dta", force
}

compress

rename ISIN isin

gen month = month(EXTRACTION_DT)
gen year  = year(EXTRACTION_DT)

gen datum = 100*year + month

drop year month

save "${Path}Data\SecurityLevelEffects\CSDB_Full.dta", replace


******* merge data

clear 
import delimited "${Path}Data\SecurityLevelEffects\SecurityLevel_Returns.txt"

merge 1:1 isin datum using "${Path}Data\SecurityLevelEffects\CSDB_Full.dta"

keep if _merge == 3
drop _merge

save "${Path}Data\SecurityLevelEffects\SecurityLevel_Returns_Full.dta", replace
